package db.jdbc;

import db.DataBaseConnectionPool;
import db.dto.AbstractDto;
import db.dto.DepartmentDto;
import db.exceptions.DaoException;
import db.exceptions.DepartmentDaoException;
import org.apache.log4j.Logger;
import org.h2.jdbc.JdbcPreparedStatement;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 * User: Vanil
 * Date: 24.12.2010
 * Time: 4:42:44
 */
public abstract class AbstractDao {
    	/**
	 * The factory class for this DAO has two versions of the create() method - one that
takes no arguments and one that takes a Connection argument. If the Connection version
is chosen then the connection will be stored in this attribute and will be used by all
calls to this DAO, otherwise a new Connection will be allocated for each operation.
	 */
	protected java.sql.Connection userConn;
    protected static final Logger logger = Logger.getLogger( DepartmentDaoImpl.class );
	/**
	 * Inserts a new row in the DEPARTMENT table.
	 */
    abstract PreparedStatement populateInsertStatement(AbstractDto dto) throws DaoException;
    abstract PreparedStatement populateUpdateStatement(int pk, AbstractDto dto) throws DaoException;
    abstract PreparedStatement populateDeleteStatement(int pk) throws DaoException;
    abstract AbstractDto[] fetchMultiResults(ResultSet rs) throws DaoException;

	public int insert(AbstractDto dto) throws DaoException
	{
		long t1 = System.currentTimeMillis();
        PreparedStatement stmt = null;
		// declare variables
        boolean connectionWasNull = false;
        try{

            if(userConn==null||userConn.isClosed())
            {
                connectionWasNull=true;
                userConn = DataBaseConnectionPool.getConnection();
            }

            stmt = populateInsertStatement(dto);
            int rows = stmt.executeUpdate();
            int affectedRowNumber=-1;
            ResultSet generatedKeys = stmt.getGeneratedKeys();
            if (generatedKeys.next()) {
                affectedRowNumber = generatedKeys.getInt(1);
            }
            //int nextId = ((JdbcPreparedStatement) stmt).getNextId(1);
            
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
            }
            return affectedRowNumber!=-1?affectedRowNumber:dto.getId();
        }
        catch (Exception _e) {
            logger.error( "Exception: " + _e.getMessage(), _e );
            throw new DaoException( "Exception: " + _e.getMessage(), _e );
        }
        finally {
            DataBaseConnectionPool.silentClose(stmt);
            if(connectionWasNull==true)
            {
                DataBaseConnectionPool.silentClose(userConn);
            }
        }

	}

    public void update(int pk, AbstractDto dto) throws DaoException
	{
		long t1 = System.currentTimeMillis();
        PreparedStatement stmt = null;
		// declare variables
        boolean connectionWasNull = false;
        try{

            if(userConn==null||userConn.isClosed())
            {
                connectionWasNull=true;
                userConn = DataBaseConnectionPool.getConnection();
            }

            stmt = populateUpdateStatement(pk,dto);
            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error( "Exception: " + _e.getMessage(), _e );
            throw new DaoException( "Exception: " + _e.getMessage(), _e );
        }
        finally {
            DataBaseConnectionPool.silentClose(stmt);
            if(connectionWasNull==true)
            {
                DataBaseConnectionPool.silentClose(userConn);
            }
        }

	}
    public void delete(int pk) throws DaoException
	{
		long t1 = System.currentTimeMillis();
        PreparedStatement stmt = null;
		// declare variables
        boolean connectionWasNull = false;
        try{

            if(userConn==null||userConn.isClosed())
            {
                connectionWasNull=true;
                userConn = DataBaseConnectionPool.getConnection();
            }

            stmt = populateDeleteStatement(pk);
            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error( "Exception: " + _e.getMessage(), _e );
            throw new DaoException( "Exception: " + _e.getMessage(), _e );
        }
        finally {
            DataBaseConnectionPool.silentClose(stmt);
            if(connectionWasNull==true)
            {
                DataBaseConnectionPool.silentClose(userConn);
            }
        }

	}

    public AbstractDto[] findByDynamicSelect(String sql, Object[] sqlParams) throws DaoException
	{
		// declare variables
		ResultSet rs = null;
		PreparedStatement stmt = null;
        boolean connectionWasNull = false;
        try{

            if(userConn==null||userConn.isClosed())
            {
                connectionWasNull=true;
                userConn = DataBaseConnectionPool.getConnection();
            }
			// construct the SQL statement
			final String SQL = sql;


			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}

			// prepare statement
			stmt = userConn.prepareStatement( SQL );

			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}

			rs = stmt.executeQuery();

			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new DepartmentDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
            DataBaseConnectionPool.silentClose(stmt);
            if(connectionWasNull==true)
            {
                DataBaseConnectionPool.silentClose(userConn);
            }
        }

	}
    public AbstractDto[] findByDynamicWhere(String sqlSelect, String sql, Object[] sqlParams) throws DaoException
	{
		// declare variables
        ResultSet rs = null;
		PreparedStatement stmt = null;
        boolean connectionWasNull = false;
        try{

            if(userConn==null||userConn.isClosed())
            {
                connectionWasNull=true;
                userConn = DataBaseConnectionPool.getConnection();
            }
			// construct the SQL statement
			final String SQL = sqlSelect + " WHERE " + sql;


			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}

			// prepare statement
			stmt = userConn.prepareStatement( SQL );

			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}


			rs = stmt.executeQuery();

			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new DepartmentDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
            DataBaseConnectionPool.silentClose(stmt);
            if(connectionWasNull==true)
            {
                DataBaseConnectionPool.silentClose(userConn);
            }
        }

	}
}
